import pyquery 
import sys,urllib
import datetime
import chardet
import MySQLdb  


def InsertContestDup( fields ):
    # Prepare SQL query to INSERT a record into the database.
    sql = "INSERT INTO `topcoder`.`contest` (`contestname`, \
             `registerby`, `submitby`, `payment`, `technology`, \
             `me`, `registrants`, `submissions`) VALUES \
             ( '%s',' %s','%s','%s','%s','%s','%s',%d)  ON DUPLICATE KEY UPDATE registerby = '%s',me='%s'" \
    % (fields[0], fields[3],fields[4], fields[5], fields[6], fields[7],fields[8],int(fields[9]),fields[3],fields[7])
   
    try:
       #print "sqltext:",sql
       # Execute the SQL command
       cursor.execute(sql)
       # Commit your changes in the database
       db.commit()
       print "Suc insert"
    except Exception, e:
       print "Exception:",e
       # Rollback in case there is any error
       db.rollback()

def InsertContestReplace( fields ):
    # Prepare SQL query to INSERT a record into the database.
    sql = "INSERT INTO `topcoder`.`contest` (`contestname`, \
             `registerby`, `submitby`, `payment`, `technology`, \
             `me`, `registrants`, `submissions`) VALUES \
             ( '%s',' %s','%s','%s','%s','%s','%s',%d)  ON DUPLICATE KEY UPDATE registerby = '%s',me='%s'" \
    % (fields[0], fields[3],fields[4], fields[5], fields[6], fields[7],fields[8],int(fields[9]),fields[3],fields[7])
   
    try:
       #print "sqltext:",sql
       # Execute the SQL command
       cursor.execute(sql)
       # Commit your changes in the database
       db.commit()
       print "Suc insert"
    except Exception, e:
       print "Exception:",e
       # Rollback in case there is any error
       db.rollback()
#
try:
    db = MySQLdb.connect(host='localhost',user='root',passwd='StanFord7',db='topcoder')
except Exception, e:
    print e
#conn = MySQLdb.connect('localhost','root','StanFord7','topcoder')   
cursor = db.cursor()    

# Prepare SQL query to INSERT a record into the database.
sql = """insert into topcoder.contest values('QuickBooks -fdhdsa', 
         '11.03.2011 00:42 EDT', '11.06.2011 00:47 EDT', '$1,500.00', 
         'J2EE', '675', '9/2', 0)"""
try:
   # Execute the SQL command
   cursor.execute(sql)
   # Commit your changes in the database
   db.commit()
   print "Suc insert"
except Exception, e:
   print "Exception:",e
   # Rollback in case there is any error
   db.rollback()


# Prepare SQL query to INSERT a record into the database.
sql = "SELECT * FROM contest "#\"
 #      " % (1000)
try:
   # Execute the SQL command
   cursor.execute(sql)
   # Fetch all the rows in a list of lists.
   results = cursor.fetchall()
   print len(results)
   for row in results:
      fname = row[0]
      print fname
      #lname = row[1]
   #   age = row[2]
  #    sex = row[3]
 #     income = row[4]
      # Now print fetched result
  #    print "fname=%s,lname=%s,age=%d,sex=%s,income=%d" % \
   #          (fname, lname, age, sex, income )
   
except Exception, e:
    print "Exception:",e
#   print "Error: unable to fecth data"
# disconnect from server
db.close()
    